package App.Repository.impl;

import App.Model.Chat.CrmChatGroupExt;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CrmChatGroupExtDao extends JpaRepository<CrmChatGroupExt, String> {
    @Query(value = "select recid groupid,recname,objectid,chattype,ifnull(chatstageid,0) as chatstageid ," +
            "prefixname,stageid,grouptype,relatedid,'' as taskbegintime,isactive,  " +
            "(select count(1) from crm_chat_callback_message as tmp_call where  tmp_call.groupid = g.recid) as relcount,  " +
            "(select content from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as content,  " +
            " (select COALESCE((select msgtime from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid   order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1), 0))  as msgtime, " +
            " (select COALESCE((select msgseq from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid  order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1), 0))  as msgseq, " +
            " (select msgcontent from crm_chat_callback_message as tmp_call where tmp_call.groupid = g.recid order by tmp_call.msgtime desc,tmp_call.msgseq desc limit 1 ) as msgcontent " +
            " from crm_chat_group g  where recstatus=1 and recid in(?1) ?2  ", nativeQuery = true)
    List<CrmChatGroupExt> findByGroupId(List<String> groupIds, String suffix);

    @Query(value = "select count(1) from crm_chat_group g  where recstatus=1 and recid in(?1)", nativeQuery = true)
    int findCountByGroupId(List<String> groupIds);
}
